package com.zking.frame.TJ.JDBC;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.zking.bean.Orders;
import com.zking.frame.LYK.JDBC;

public class baseDao {
	private static JDBC_Test jdbc = new JDBC_Test();

	
	public static com.zking.bean.User findUsername(String sql) {
		Connection conn = jdbc.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int id = rs.getInt("user_id");
				String username = rs.getString("username");
				String password = rs.getString("password");
				String sex = rs.getString("sex");
				String autograph = rs.getString("autograph");
				String phone = rs.getString("phone");
				String brithday = rs.getString("brithday");
				double balance = rs.getDouble("balance");
				String headPic = rs.getString("headPic");
				com.zking.bean.User user = new com.zking.bean.User(id,username,password,sex,autograph,phone,brithday,balance,headPic);
				return user;				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	
	public static void addUser(String sql) {
		Connection conn = jdbc.getConnection();
		Statement st = null;
		try {
			st = conn.createStatement();
			st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static List<String> findAllUsername() {
		Connection conn = jdbc.getConnection();
		Statement st = null;
		ResultSet rs = null;
		List <String> names = new ArrayList<String>();
		String sql = "select username from user";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()) {
				String username = rs.getString("username");
				names.add(username);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return names;
	}
	
	public static List<String> findAllPhone() {
		Connection conn = jdbc.getConnection();
		Statement st = null;
		ResultSet rs = null;
		List <String> phones = new ArrayList<String>();
		String sql = "select phone from user";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()) {
				String phone = rs.getString("phone");
				phones.add(phone);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return phones;
	}
	
	public static void updatePerson(String sex ,String brithday,String autograph ,int user_id){
		String sql="update user set sex= '"+sex+"', autograph= '"+autograph+"',brithday= '"+brithday+"' where user_id=" +user_id;
		Connection conn=JDBC.getConn();
		Statement st = null;
		try {
			st = conn.createStatement();
			st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	//更换头像
	public static void updateHeadPic(String headPic ,int uid) {
		String sql="update user set headPic= '"+headPic+"'where user_id=" +uid;
		Connection conn=JDBC.getConn();
		Statement st = null;
		try {
			st = conn.createStatement();
			st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	//查找订单
	public static List<com.zking.bean.Orders> findOrder(int uid){
		List <com.zking.bean.Orders> list = new ArrayList<com.zking.bean.Orders>();
//		String sql="update user set headPic= '"+headPic+"'where user_id=" +uid;
		String sql= "select * from orderall where user_id ="+uid;
		Connection conn=JDBC.getConn();
		ResultSet rs = null;
		Statement st = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int id = rs.getInt("order_id");
				int uids = rs.getInt("user_id");
				String film_name = rs.getString("film_name");
				String time = rs.getString("time");
				String cinema_hall = rs.getString("cinema_hall");
				String seat = rs.getString("seat");
				double price = rs.getDouble("price");
				String order_state = rs.getString("order_state");
				String film_src = rs.getString("film_src");
				list.add(new Orders(id,uids,film_name,time,cinema_hall,seat,price,order_state,film_src));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				st.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public static void deletOrder(int oid) {
		String sql = "DELETE FROM orderAll WHERE order_id="+oid;
		Connection conn=JDBC.getConn();
		Statement st = null;
		try {
			st = conn.createStatement();
			st.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
}
